﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DotSpatial.Controls;
using HydroDesktop.Interfaces.ObjectModel;
using System.Data;
using HydroDesktop.Database;
using HydroDesktop.Interfaces;

namespace DroughtAnalysis
{
    /// <summary>
    /// Finds a station that has all required variables
    /// </summary>
    public class StationFinder
    {
        private IList<Site> _suitableSites = null;
        private Variable _temperatureVariable = null;
        private Variable _precipVariable = null;
        
        /// <summary>
        /// Creates a new instance of the station finder class
        /// </summary>
        /// <param name="map">The map of the application</param>
        public StationFinder(IMap map)
        {
            MainMap = map;
        }

        /// <summary>
        /// Gets or sets the main map where stations are found
        /// </summary>
        public IMap MainMap { get; set; }
        
        /// <summary>
        /// Finds all stations that measure temperature and precipitation
        /// </summary>
        /// <returns></returns>
        public IList<Site> FindSuitableStations()
        {
            Variable tmpVa = GetTemperatureVariable();
            Variable pcpVa = GetPrecipitationVariable();
            if (tmpVa == null || pcpVa == null) return new List<Site>();


            var repo = RepositoryFactory.Instance.Get<HydroDesktop.Interfaces.ISitesRepository>(DatabaseTypes.SQLite, HydroDesktop.Configuration.Settings.Instance.DataRepositoryConnectionString);
            IList<Site> suitableSites = repo.GetSitesWithBothVariables(tmpVa, pcpVa);
            
            _suitableSites = suitableSites;
            return suitableSites;
        }

        public DateTime FindMinimumSuitableDate(string minOrMax, string beginOrEnd)
        {
            if (_temperatureVariable == null) _temperatureVariable = GetTemperatureVariable();
            if (_precipVariable == null) _precipVariable = GetPrecipitationVariable();
            if (_suitableSites == null) _suitableSites = FindSuitableStations();

            if (_suitableSites == null || _temperatureVariable == null || _precipVariable == null)
            {
                //special code..
                return DateTime.Now.AddYears(-5);
            }
            
            //construct qry
            if (_suitableSites.Count == 0) return DateTime.MinValue;
            StringBuilder qry = new StringBuilder ("s.SiteCode IN ('" + _suitableSites[0].Code + "'");
            for(int i=1; i< _suitableSites.Count; i++)
            {
                qry.Append(",'").Append(_suitableSites[i].Code + "'");
            }

            string sqlStr = string.Format("SELECT {0}({1}) FROM DataSeries ds INNER JOIN Sites s ON ds.SiteID = s.SiteID " +
                "WHERE ", minOrMax, beginOrEnd) +
                qry.ToString() + ")" + string.Format(" AND VariableID IN ({0}, {1})", _precipVariable.Id,
                _temperatureVariable.Id);

            DbOperations db = new DbOperations(HydroDesktop.Configuration.Settings.Instance.DataRepositoryConnectionString, DatabaseTypes.SQLite);
            object res = db.ExecuteSingleOutput(sqlStr);
            return Convert.ToDateTime(res);
        }

        /// <summary>
        /// Gets the table of suitable variables
        /// </summary>
        /// <param name="filter">the filter condition (must be a compatible SQL expression)</param>
        /// <returns>The matching variables table</returns>
        private DataTable GetVariableTable(string filter)
        {
            //finds the variables that fulfill the filter conditions
            string sqlstr = "SELECT VariableID, VariableName, VariableCode, DataType, ValueType, Speciation, SampleMedium, " +
                "TimeSupport, GeneralCategory, NoDataValue, " +
                "units1.UnitsName as 'VariableUnitsName', units2.UnitsName as 'TimeUnitsName' FROM Variables " +
                "INNER JOIN Units units1 ON Variables.VariableUnitsID = units1.UnitsID " +
                "INNER JOIN Units units2 ON Variables.TimeUnitsID = units2.UnitsID WHERE " +
                filter;

            DbOperations db = new DbOperations(HydroDesktop.Configuration.Settings.Instance.DataRepositoryConnectionString, DatabaseTypes.SQLite);
            return db.LoadTable(sqlstr);
        }

        private Variable VariableFromDataRow(DataRow row)
        {
            Unit timeUnit = Unit.UnknownTimeUnit;
            timeUnit.Name = (string)row["TimeUnitsName"];

            Unit variableUnit = Unit.Unknown;
            variableUnit.Abbreviation = (string)row["VariableUnitsName"];

            Variable v = new Variable();
            v.Id = (long)row["VariableID"];
            v.Name = (string)row["VariableName"];
            v.Code = (string)row["VariableCode"];
            v.DataType = (string)row["DataType"];
            v.ValueType = (string)row["ValueType"];
            v.Speciation = row["Speciation"] == DBNull.Value ? null : (string)row["Speciation"];
            v.SampleMedium = row["SampleMedium"] == DBNull.Value ? null : (string)row["SampleMedium"];
            v.TimeSupport = row["TimeSupport"] == DBNull.Value ? 0 : (double)row["TimeSupport"];
            v.VariableUnit = variableUnit;
            v.TimeUnit = timeUnit;
            v.GeneralCategory = row["GeneralCategory"] == DBNull.Value ? null : (string)row["GeneralCategory"];
            v.NoDataValue = (double)row["NoDataValue"];
            return v;
        }

        /// <summary>
        /// Gets the variable object that measures temperature
        /// </summary>
        /// <returns>The variable object</returns>
        public Variable GetTemperatureVariable()
        {
            DataTable tab = GetVariableTable("VariableName LIKE 'temperatur%' AND DataType = 'Average'");
            if (tab.Rows.Count > 0)
            {
                _temperatureVariable = VariableFromDataRow(tab.Rows[0]);
                return _temperatureVariable;
            }
            else
                return null;
        }

        /// <summary>
        /// Gets the variable object that measures precipitation
        /// </summary>
        /// <returns>The precipitation variable object</returns>
        public Variable GetPrecipitationVariable()
        {
            DataTable tab = GetVariableTable("VariableName LIKE 'precip%'");
            if (tab.Rows.Count > 0)
            {
                _precipVariable = VariableFromDataRow(tab.Rows[0]);
                return _precipVariable;
            }
            else
                return null;
        }
    }
}
